package com.crazy.poi.excel;

import java.io.FileOutputStream;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelService {
	
	private Workbook wb;

	public ExcelService newExcel() throws Exception {
		if (wb == null) {
			wb = new XSSFWorkbook();
		}
		return this;
	}
	
	public ExcelService createSheet() throws Exception {
		wb.createSheet("sheet one");
		// 使用工具类创建名字，可以自动处理掉不合法的字符
	    String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]");
	    wb.createSheet(safeName);
	    return this;
	}
	
	public ExcelService createCell() throws Exception {
		Sheet sheet = wb.getSheet("sheet one");
		CreationHelper createHelper = wb.getCreationHelper();
		
	    Row row = sheet.createRow(0);
	    row.createCell(0).setCellValue(1);
	    row.createCell(1).setCellValue("abcd");
	    
	    // 设置列宽，20 * 256 意思是 20个字符的宽度
	    sheet.setColumnWidth(2, 20 * 256);
	    Cell cell2 = row.createCell(2);
	    cell2.setCellValue(new Date());
	    // 设置日期显示格式
	    CellStyle cellStyle = wb.createCellStyle();
	    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:MM:ss"));
	    cell2.setCellStyle(cellStyle);
	    return this;
	}
	
	public void writeFile() throws Exception {
		FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
		wb.write(fileOut);
		fileOut.close();
	}
}
